package com.hx.utils;

//导入相关的包 mysql
import java.sql.*;
//导入操作Excel的包
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import org.apache.commons.lang3.StringUtils;

import java.io.File;
import java.io.IOException;

/**
 * @Auther: QZ
 * @Date: 2022/6/23 - 06 - 23 - {TIME}
 * @version: 1.0
 */
//excel 读取类
public class excelRead{
/*    private static String driver = "com.mysql.cj.jdbc.Driver";
    private static String url = "jdbc:mysql://localhost:3306/atuo_sql";
    private static String user = "root";
    private static String password = "qazwsx";*/

    private static String driver="com.mysql.cj.jdbc.Driver";
    private static String url="jdbc:mysql://localhost:3306/auto_sql?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC" ;
    private static String user="root";
    private static String password="qazwsx";
    private static Connection con = null;
    private static PreparedStatement pstatement=null;
    public static int readExcel(String tablename,File path) throws BiffException,IOException{
        int res = 1;
        //连接数据库
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
        try {
            con = DriverManager.getConnection(url,user,password);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        if(con!=null) {
            System.out.println("sucess connect to mysql");
        }
        //读取excel数据表
        Workbook workbook = Workbook.getWorkbook(path);
        Sheet[] sheets = workbook.getSheets();
        if(sheets!=null)
        {
            for(Sheet sheet:sheets)
            {
                //获取行数
                int rows = sheet.getRows();
                System.out.println(rows);
                //获取列数
                int cols = sheet.getColumns();
                System.out.println(cols);

                 //Class<?> clazz = tablename.class;
                 //int length = clazz.getDeclaredFields().length;
                String str = "";
                for (int i = 0; i < cols; i++) {
                    str += "?,";
                }
                str = StringUtils.chop(str);//[中]从字符串中删除最后一个字符。
                //读取数据
                for(int row = 1;row<rows;row++)  //z这里row从2开始是因为去除了表头占的两行
                {
                    String values[] = new String[rows];
                    for(int col=0;col<cols;col++)
                    {
                        //将每行不同列的内容放入数组
                        values[col] = sheet.getCell(col,row).getContents();
                    }
                    //将读取出来的内容写入mysql数据库
                    try {
                        pstatement = con.prepareStatement("insert "+tablename+" values("+str+");");
                        for (int i = 0; i < cols; i++) {
                            pstatement.setNString(i+1, values[i]);
                        }

                        Statement st = con.createStatement();
                        String sql1 = "show tables";

                        ResultSet re = st.executeQuery(sql1);
                        while (re.next()){
                            String test = re.getString("Tables_in_auto_sql");
                            if(tablename.equals(test)){
                                res = 0;
                            }
                        }
                        //pstatement.setNString(1, values[0]);
                        //pstatement.setNString(2,values[1]);
                        //pstatement.setNString(3,values[2]);
                        pstatement.executeUpdate();  //执行sql语句插入内容
                    } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                }
            }
        }
        workbook.close();
        try {
            con.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return res;
    }
}
/*class testExcel2Mysql {
    public static void main(String[] args) throws Exception {
        File path = new File("E:\\atuosql.xls");
        excelRead.readExcel(path);  // 传入要操作的excel路径
    }
}*/
